package email;

import java.io.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import java.util.Date;

import javax.mail.*;
import javax.mail.internet.MimeBodyPart;

import com.sun.mail.imap.IMAPFolder;
import com.sun.mail.pop3.POP3Folder;
import com.sun.mail.pop3.POP3Store;

public class ReceiveEmail {
	private static final String CONFIG_FILE = "/email.properties";
	private static final java.util.Properties appCfg = new java.util.Properties();
	
	public static void main(String[] args) {
		
		try {
			InputStream stream = SendEmail.class.getResourceAsStream(CONFIG_FILE);
			if (stream != null){
				appCfg.load(stream);
			} else {
				System.out.println("SendEmail Input Stream is NULL");
			}
		} catch (Exception e) {
			System.err.println("SendEmail Error loading file " + CONFIG_FILE);
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		
		//POP3 Protocol
		/*String mailHost = "pop.gmail.com";
		String mailStoreType = "pop3";*/
		
		//IMAP Protocol
		String mailHost = appCfg.getProperty("mailHost");
		String mailStoreType = appCfg.getProperty("mailStoreType");
		
		//Email address and password
		String mailUser = appCfg.getProperty("mailUser");
		String mailPassword = appCfg.getProperty("mailPassword");
		
		receiveEmail(mailHost, mailStoreType, mailUser, mailPassword);
				
	}
	
	public static void receiveEmail(String host, String storeType, String user, String password) {
		System.out.println(currentTimestamp() + " : Attempting login...");
		try {
				Properties properties = new Properties();
			
				//POP3 Protocol
				/*properties.put("mail.pop3.host", host);
				properties.put("mail.pop3.socketFactory.class", "javax.net.ssl.SSLSocketFactory");
				properties.put( "mail.pop3.socketFactory.fallback", "true");
				properties.put( "mail.pop3.port", "993");
				properties.put( "mail.pop3.socketFactory.port", "995");*/
				
				//IMAP Protocol
				properties.setProperty("mail.store.protocol", storeType);
				properties.setProperty("mail.imaps.partialfetch", "false");
			
				Session emailSession = Session.getDefaultInstance(properties);
			
				Store emailStore = emailSession.getStore(storeType);
				emailStore.connect(host, user, password);
			
				try {
					//POP3 Protocol
					/*POP3Folder emailFolder = (POP3Folder) emailStore.getFolder("INBOX");*/
			
					//IMAP Protocol
					IMAPFolder emailFolder = (IMAPFolder) emailStore.getFolder("INBOX");
					emailFolder.open(Folder.READ_ONLY);
					
					try {
						System.out.println(currentTimestamp() + " : Logged in successfully as " + user + "!");
				
						Message[] messages = emailFolder.getMessages();
						int len = messages.length;
				
						System.out.println(currentTimestamp() + " : Checking for unread emails...");
						
						String index = get_last_checked_email_index();
						
						long index_value = 0;
						if (index != "false" || index != null){
							index_value = Long.valueOf(index);
						}
						
						for (int i = 0; i < len; i++) {
							Message message = messages[i];
					
							//POP3 Protocol
							/*Date index_value = new Date();
							Date email_index = new Date();
							
							if (index != "false" || index != null){
								SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
								Date parsed_date = new Date();
								try {
									parsed_date = format.parse(index);
								} catch (ParseException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
								index_value = new java.sql.Date(parsed_date.getTime());
								
								email_index = new java.sql.Date(message.getSentDate().getTime());
							}*/
							
							//IMAP Protocol
							long email_index = emailFolder.getUID(message);
							
							//POP3 Protocol
							//if (email_index.compareTo(index_value)>=0){
							
							//IMAP Protocol	
							if (email_index > index_value){
								
								String index_string = String.valueOf(email_index);
								
								update_email_index(index_string);
								System.out.println(currentTimestamp() + " : Reading '" + message.getSubject() +"'...");
								String c_id = check_exist_in_CASEMAST(message.getSubject());
								
								if (c_id != "false"){
									Multipart multipart = (Multipart)message.getContent();
									
									for(int j = 0; j < multipart.getCount(); j++){
										MimeBodyPart part = (MimeBodyPart) multipart.getBodyPart(j);
				                        
										if (Part.ATTACHMENT.equalsIgnoreCase(part.getDisposition())) {
				                        	int id = Integer.parseInt(c_id);
				                        	String file = part.getContentType();
				                        	String[] file_content_type = file.split(";");
				                        	String file_mime_type = file_content_type[0];
				                        	String file_name = part.getFileName();
				                        	int file_size = part.getSize();
				                        	
				                        	InputStream stream = part.getInputStream();
				                        	
				                            byte[] buffer = new byte[4096];
				                            ByteArrayOutputStream baos = new ByteArrayOutputStream();
				                           
				                            int bytesRead = stream.read(buffer);
				                            
				                            while (bytesRead != -1) {
				                                baos.write(buffer, 0, bytesRead);
				                                bytesRead = stream.read(buffer);
				                            }
				                            
				                            byte[] attachment_data = baos.toByteArray();
				                        	
				                        	create_CASEDOC (id, file_name, attachment_data, file_mime_type, file_size);
				                        }
									}
								} else {
									System.out.println(currentTimestamp() + " : Non-related email.");
								}
							}
						}
					} finally {
						emailFolder.close(false);
					}
				} finally {
					emailStore.close();
				}
				
				System.out.println(currentTimestamp() + " : Done!");
		
		}  catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static String check_exist_in_CASEMAST(String Ref_No){
		try {
			String rtn = "false";
			boolean ok = DBEmail.connect();
			if(ok == false){
				System.out.println(SendEmail.currentTimestamp() + " : " + DBEmail.getMessage());
				return rtn;
			}
			
			Connection connection = DBEmail.getConnection();
			
			try {
				String sql = "SELECT * FROM CASEMAST WHERE C_REFERENCE_NO = ?";
				PreparedStatement stmt = null;
		        ResultSet rs =null;
		        try {
		        		stmt = connection.prepareStatement(sql);
		        		stmt.setString(1, Ref_No);
		        		rs = stmt.executeQuery();
		        		try {
		        			if (rs.next()){
		        				rtn = rs.getString("C_ID");
		        			}
		        		} finally {
		        			rs.close();
		        		}
		        } finally {
					stmt.close();
		        }
			} finally {
				connection.close();
			}
			
			return rtn;
			
		} catch (Exception e) {
			System.out.println(currentTimestamp() + " : " + e);
            return e.toString();
		}
	}
	
	public static String get_last_checked_email_index(){
		try {
			String rtn = "false";
			boolean ok = DBEmail.connect();
			if(ok == false){
				System.out.println(SendEmail.currentTimestamp() + " : " + DBEmail.getMessage());
				return rtn;
			}
			
			Connection connection = DBEmail.getConnection();
			
			try {
				String sql_get_row = "SELECT * FROM EMAIL_INDEX";
				PreparedStatement stmt = null;
		        ResultSet rs =null;
		        
		        try {
		        	stmt = connection.prepareStatement(sql_get_row);
		        	rs = stmt.executeQuery();
		        	try {
			        	if (rs.next()){
			        		rtn = rs.getString("E_INDEX");
			        	} else {
			        		rtn = "false";
			        	}
		        	} finally {
		        		rs.close();
		        	}
		        } finally {
		        	stmt.close();
		        }
			} finally {
				connection.close();
		    }
			
			return rtn;
			
		} catch (SQLException e) {
			System.out.println(currentTimestamp() + " : " + e);
            return e.toString();
		}
		
	}
	
	public static void update_email_index(String index){
		try {
			boolean ok = DBEmail.connect();
			if(ok == false){
				System.out.println(SendEmail.currentTimestamp() + " : " + DBEmail.getMessage());
				return;
			}
		
			Connection connection = DBEmail.getConnection();
			
			try {
				String sql_check_row_exist = "SELECT * FROM EMAIL_INDEX";
				PreparedStatement stmt_exist = null;
				try {
			        ResultSet rs_exist = null;
			        try {
				        String sql = "";
			        	stmt_exist = connection.prepareStatement(sql_check_row_exist);
			        	rs_exist = stmt_exist.executeQuery();
			        	if (rs_exist.next()){
			        		sql = "UPDATE EMAIL_INDEX SET E_INDEX = ?";
			        	} else {
			        		sql = "INSERT INTO EMAIL_INDEX (E_INDEX) VALUES (?)";
			        	}
			        	PreparedStatement stmt = null;
				        ResultSet rs =null;
				        try {
							stmt = connection.prepareStatement(sql);
							stmt.setString(1, index);
							try {
								rs = stmt.executeQuery();
							} finally {
								rs.close();
							}
				        } finally {
							stmt.close();
				        }
					} finally {
						rs_exist.close();
					}
				} finally {
					stmt_exist.close();
				}
			} finally {
				connection.close();
			}	
				
        } catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void create_CASEDOC (int CD_CASE_ID, String CD_NAME, byte[] CD_DATA, String CD_TYPE, int CD_LENGTH){
		System.out.println(currentTimestamp() + " : Attempting to download attachment...");
		try {
			boolean ok = DBEmail.connect();
			if(ok == false){
				System.out.println(SendEmail.currentTimestamp() + " : " + DBEmail.getMessage());
				return;
			}
	
			Connection connection = DBEmail.getConnection();
			
			try {
	    		String sql = "INSERT INTO CASEDOC (CD_CASE_ID, CD_NAME, CD_DATA, CD_CREATED_DATE, CD_CREATED_BY, CD_TYPE, CD_LENGTH, CD_REF_NO) "
	    					+ "VALUES (?, ?, empty_blob(), sysdate, ?, ?, ?, ?)";
		        PreparedStatement stmt = null;
		        try {
			        ResultSet rs = null;
			        try {
						stmt = connection.prepareStatement(sql);
						stmt.setInt(1, CD_CASE_ID);
						stmt.setString(2, CD_NAME);
						stmt.setString(3, "_SYSTEM_");
						stmt.setString(4, CD_TYPE);
						stmt.setInt(5, CD_LENGTH);
						stmt.setString(6, null);
						rs = stmt.executeQuery();
			        } finally {
			        	rs.close();
			        }
		        } finally {
					stmt.close();
		        }
					
				Long doc_id = null;
				Statement stmt2 = connection.createStatement();
				try {
					ResultSet rs = null;
					try {
						rs = stmt2.executeQuery("select seq_casedoc.currval from dual");
						if(rs.next()){
							doc_id = new Long(rs.getLong(1));
						}
					} finally {
						rs.close();
					}
				} finally {
					stmt2.close();
				}
					
				sql = "select cd_data from casedoc where cd_id = ? for update";
					
				stmt = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				stmt.setLong(1, doc_id);
				try {
					ResultSet rs = null;
					try {
						rs = stmt.executeQuery();
						if(rs.next()){
							Blob blob = rs.getBlob("cd_data");
							blob.truncate(0);
							blob.setBytes(1, CD_DATA);
							rs.updateBlob("cd_data", blob);
							rs.updateRow();
						}
					} finally {
						rs.close();
					}
				} finally {
					stmt.close();
				}
			} finally {
				connection.close();
			}
			
			System.out.println(currentTimestamp() + " : Attachment download completed!");
				
	        } catch (SQLException e) {
				e.printStackTrace();
			} 
	}
	
	public static String currentTimestamp(){
		Date date = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy h:mm:ss a");
		String currentTimestamp = sdf.format(date);
		return currentTimestamp;
	}
}
